package com.edeweiss.community.dao.daoImp;

import com.edeweiss.community.dao.StudentDao;
import com.edeweiss.community.domain.*;
import com.edeweiss.community.web.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class StudentDaoImp implements StudentDao {

    private QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    //学生登陆
    @Override
    public Student loginStudent(Student student) {

        //判断用户名密码是否正确
        String sql="SELECT * FROM student WHERE sno= ? and spassword= ?";
        try{
            Student query = qr.query(sql, new BeanHandler<Student>(Student.class), student.getSno(), student.getSpassword());
            return query;
        }catch(SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }

    }

    @Override
    public int ReginStudent(Student s) {
        int i = 0;
        String sql = "INSERT INTO student(sid,susername,spassword,ssex,sfaculty,sno,stelphone,sbirthday) VALUES(?,?,?,?,?,?,?,?)";
        Object[] params = {s.getSid(),s.getSusername(),s.getSpassword(),s.getSsex(),s.getSfaculty(),s.getSno(),s.getStelphone(),s.getSbirthday()};
        try{
            i = qr.update(sql,params);
        }catch(SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public int ChangePassword(Student student,String newPassword) {
        String sql = "UPDATE student SET spassword=? where sno=? and spassword = ?";
        int i = 0;
        try{
           i = qr.update(sql,newPassword,student.getSno(),student.getSpassword());
        }catch(SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<Faulty> findFaultyId() {
        List<Faulty> faulty = null;
        String sql = "select * from faculty";
        try{
            faulty = qr.query(sql,new BeanListHandler<Faulty>(Faulty.class));
        }catch(SQLException e) {
            e.printStackTrace();
        }
        return faulty;
    }

    @Override
    public List<Community> findJoinCommunity(String sid) {
        List<Community> communities = null;
        String sql = "select c.cname,c.ctime,c.cintroduce,c.cteacher,c.responsibility  from community_member as cm,community as c WHERE cm.cid = c.cid and cm.sid = ?";
        try{
            communities = qr.query(sql,new BeanListHandler<Community>(Community.class),sid);
            System.out.println(communities);
        }catch(SQLException e) {
            e.printStackTrace();
        }
        return communities;
    }

    @Override
    public int applyJoinCommunity(CommunityApply apply) {
        int i = 0;
        String sql = "insert into community_apply (sid,name,resume,status,time) values (?,?,?,?,?)";
        Object[] params = {apply.getSid(),apply.getName(),apply.getResume(),apply.getStatus(),apply.getTime()};
        try{
            i = qr.update(sql,params);
        }catch(SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public int applyHof(Hof hof) {
        String sql = "insert into hof (sid,experience,date,status,img) values (?,?,?,?,?)";
        Object[] params = {hof.getSid(),hof.getExperience(),hof.getDate(),hof.getStatus(),hof.getImg()};
        int i = 0;
        try{
            i = qr.update(sql,params);
        }catch(SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<Message> findMessage(String sid) {
        List<Message> messages = null;
        String sql1 = "select * from message where sid=? and status=0";
        String sql2 = "update message set status = 1 where sid=?";
        Connection conn = JDBCUtils.getConnection();
        try{
            JDBCUtils.startTransaction(conn);
            messages = qr.query(conn, sql1, new BeanListHandler<Message>(Message.class),sid);
            qr.update(conn,sql2,sid);
            JDBCUtils.commitTransaction(conn);
        }catch (Exception e){
            JDBCUtils.rollbackTransaction(conn);
            e.printStackTrace();
        }
        return messages;
    }
}
